package cn.itlaobing.eshop.dao;

import cn.itlaobing.eshop.entity.BaseGoods;
import cn.itlaobing.eshop.entity.Itlb_goods;
import cn.itlaobing.eshop.entity.SaleDTO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import cn.itlaobing.eshop.entity.ItlbFocuss;


import java.sql.SQLException;
import java.util.List;


/**
 * @Description:
 * @Author: WangDong
 * @Date: 2017/11/13 17:22
 */
public class GoodsDao extends BaseDao {

    public List<BaseGoods> QueryAll() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="select * from base_goods";
        BeanListHandler<BaseGoods> beanListHandler=new BeanListHandler<BaseGoods>(BaseGoods.class);
        return queryRunner.query(sql,beanListHandler);
    }

    /*public Map<String,Object> queryCounts() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="SELECT g.`id`, g.`price` , g.`good_name`,p.`picUrl` FROM`itlb_goods` g LEFT JOIN (SELECT `good_id`, SUM(`buy_count`) counts   FROM `itlb_order_details`) s" +
                "ON g.`id`=s.`good_id` LEFT JOIN `itlb_goods_pic` p ON g.`id`=p.`goodsId`AND g.`status_line`=1 ORDER BY s.counts LIMIT 0,6";
        MapHandler mapHandler=new MapHandler();
        Map<String,Object> map=queryRunner.query(sql, new ResultSetHandler<Map<String, Object>>() {
            @Override
            public Map<String, Object> handle(ResultSet rs) throws SQLException {
                if(rs.next()){
                    Map<String,Object> map=new HashedMap();
                    map.put("id",rs.getInt(1));
                    map.put("price",rs.getDouble(2));
                    map.put("good_name",rs.getString(3));
                    map.put("picUrl",rs.getString(4));
                    return map;
                }
                return null;
            }

        });

        return map;


    }*/
    //查询最新上架商品
    public List<SaleDTO> findNew() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="SELECT g.`id`, g.`price` , g.`good_name`,p.`picUrl` FROM `itlb_goods` g LEFT JOIN `itlb_goods_pic` p ON g.`id`=p.`goodsId` AND g.`status_line`=1 AND g.`good_new`=1 LIMIT 0,6";
        BeanListHandler<SaleDTO> beanListHandler=new BeanListHandler<SaleDTO>(SaleDTO.class);
        return queryRunner.query(sql,beanListHandler);
    }
    //查询热销商品
    public List<SaleDTO> findCounts() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="SELECT g.`id`, g.`price` , g.`good_name`,p.`picUrl` FROM`itlb_goods` g LEFT JOIN (SELECT `good_id`, SUM(`buy_count`) counts   FROM `itlb_order_details`) s ON g.`id`=s.`good_id` LEFT JOIN `itlb_goods_pic` p ON g.`id`=p.`goodsId`AND g.`status_line`=1 ORDER BY s.counts LIMIT 0,6";
        BeanListHandler <SaleDTO> beanListHandler=new BeanListHandler<SaleDTO>(SaleDTO.class);
        return queryRunner.query(sql,beanListHandler);
    }
    //查询轮播图
    public List<ItlbFocuss> findPictures() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="SELECT img FROM `itlb_focuss`";
        BeanListHandler<ItlbFocuss> beanListHandler=new BeanListHandler<ItlbFocuss>(ItlbFocuss.class);
        return queryRunner.query(sql,beanListHandler);

    }
    //查询详情页
    public Itlb_goods query(int id) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(this.getDataSource());
        String sql="SELECT * FROM `itlb_goods` WHERE id=?";
        BeanHandler<Itlb_goods>beanHandler=new BeanHandler<Itlb_goods>(Itlb_goods.class);
        return queryRunner.query(sql,beanHandler,id);
    }

}

